﻿using System;
using System.Collections.Generic;
//////using System.Linq;
using System.Text;
 //Add a reference to Microsoft.SqlServer.Smo
 //Add a reference to Microsoft.SqlServer.ConnectionInfo
 //Add a reference to Microsoft.SqlServer.SqlEnum
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using QuanLyKho.DataAccess;
using QuanLyKho.Common;

namespace QuanLyKho.GUI.Library
{
    public class clsDbTool
    {
        private Server m_server = null;
        SqlConnection conn = null;
        private string connectionString = "Server=(local); Trusted_Connection=True;";
        private string ServerName = "";
        public string DbName = "";
        public string UserID = "";
        public string Pass = "";

        /// <summary>
        /// Sử dụng cho các hàm: 
        /// - getServerName(); 
        /// - getDbName(string connString, string filename)
        /// </summary>
        public clsDbTool()
        {

        }

        public clsDbTool(string server)
        {
            m_server = new Server(server);
            ServerName = server;
            connectionString = "Server=" + ServerName + "; Trusted_Connection=True;";
        }

        /// <summary>
        /// Author: Le Hoang Long
        /// Hàm lấy tên Server.
        /// </summary>
        /// <returns></returns>
        public string getServerName()
        {
            try
            {
                string serverName = null;
                conn = new SqlConnection(connectionString);
                conn.Open();

                SqlCommand cmd = new SqlCommand("SELECT CONVERT(varchar(50), SERVERPROPERTY('servername')) AS ServerName", conn);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        if (dr["ServerName"] != DBNull.Value)
                            serverName += Convert.ToString(dr["ServerName"]) + ";";
                    }
                }
 
                conn.Close();
                return serverName;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally { conn.Close(); }
        }
        /// <summary>
        /// Author: Le Hoang Long
        /// Hàm lấy lên Database trong hệ thống
        /// </summary>
        /// <param name="connString">Đường dẫn kết nối</param>
        /// <param name="filename">Tên database cần lấy</param>
        /// <returns></returns>
        public string getDbName(string filename)
        {
            try
            {
                string dbName = "";
                conn = new SqlConnection(connectionString);
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT name FROM sys.databases WHERE name=@Name", conn);
                cmd.Parameters.AddWithValue("@Name", filename);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    if (dr.Read())
                    {
                        if (dr["name"] != DBNull.Value)
                            dbName = Convert.ToString(dr["name"]);
                    }
                }

                conn.Close();
                return dbName;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally { conn.Close(); }
        }
        /// <summary>
        /// Author: Le Hoang Long
        /// Hàm backup database.
        /// </summary>
        /// <param name="connString"></param>
        /// <param name="database"></param>
        /// <param name="filename"></param>
        public void BackupDatabase(string database, string filename)
        {
            SqlConnection conn1 = null;
            StringBuilder commandText = new StringBuilder("BACKUP DATABASE @database TO DISK=@filename WITH FORMAT");
            try
            {
                conn1 = new clsConnect().GetSqlConnection();
                conn1.Open();
                SqlCommand cmd = new SqlCommand(commandText.ToString(), conn1);
                cmd.Parameters.AddWithValue("@database", database);
                cmd.Parameters.AddWithValue("@filename", filename);
                cmd.ExecuteNonQuery();
                conn1.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally { conn1.Close(); }
        }
        /// <summary>
        /// Author: Le Hoang Long
        /// Ham phục hồi database.
        /// </summary>
        /// <param name="connString"></param>
        /// <param name="database"></param>
        /// <param name="filename"></param>
        public void RestoreDatabase(string database, string filename, string moveNameMdf, string moveNameLdf, string toNameMdf, string toNameLdf)
        {
            string command = @" RESTORE DATABASE @database FROM DISK=@filename
                                WITH
                                MOVE @moveNameMdf TO @toNameMdf,
                                MOVE @moveNameLdf TO @toNameLdf,
                                RECOVERY, REPLACE";
            StringBuilder sBuilder = new StringBuilder(command);
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.Parameters.AddWithValue("@database", database);
                cmd.Parameters.AddWithValue("@filename", filename);
                cmd.Parameters.AddWithValue("@moveNameMdf", moveNameMdf);
                cmd.Parameters.AddWithValue("@moveNameLdf", moveNameLdf);
                cmd.Parameters.AddWithValue("@toNameMdf", toNameMdf);
                cmd.Parameters.AddWithValue("@toNameLdf", toNameLdf);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally { conn.Close(); }
        }
        /// <summary>
        /// Author: Le Hoang Long
        /// Attach file Database
        /// </summary>
        /// <param name="database">Tên database cần tạo</param>
        /// <param name="files">Đường dẫn của file Attach</param>
        /// <param name="options">Thuộc tính khi Attach</param>
        public void AttachDatabase(string database, StringCollection files, AttachOptions options)
        {
            m_server.AttachDatabase(database, files, options);
        }
        /// <summary>
        /// Author: Le Hoang Long
        /// Lấy thông số Server
        /// </summary>
        /// <param name="serverName">Tên server</param>
        /// <returns></returns>
        public string GetServerVersion(string serverName)
        {
            return m_server.PingSqlServerVersion(serverName).ToString();
        }
        /// <summary>
        /// Author: Le Hoang Long
        /// Đếm số kết nối được kích hoạt trong SQL
        /// </summary>
        /// <param name="database">Tên database</param>
        /// <returns></returns>
        public int CountActiveConnections(string database)
        {
            return m_server.GetActiveDBConnectionCount(database);
        }
        /// <summary>
        /// Author: Le Hoang Long
        /// Khóa mọi kết nối của database
        /// </summary>
        /// <param name="database"></param>
        public void KillAllProcess(string database)
        {
            m_server.KillAllProcesses(database);
        }
        /// <summary>
        /// Author: Le Hoang Long
        /// Xóa Database
        /// </summary>
        /// <param name="database">Tên database</param>
        public void DeleteDatabase(string database)
        {
            m_server.KillDatabase(database);
        }
        /// <summary>
        /// Author: Le Hoang Long
        /// Detach database
        /// </summary>
        /// <param name="database">Tên database</param>
        /// <param name="updateStatistics"></param>
        /// <param name="removeFullTextIndex"></param>
        public void DetachDatabase(string database, bool updateStatistics, bool removeFullTextIndex)
        {
            m_server.DetachDatabase(database, updateStatistics, removeFullTextIndex);
        }
        /// <summary>
        /// Author: Le Hoang Long
        /// Tạo Database
        /// </summary>
        /// <param name="database">Tên database</param>
        public void CreateDatabase(string database)
        {
            Database db = new Database(m_server, database);
            db.Create();
        }
        
    }
}
